package com.eportal.base;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.eportal.domain.Sysmenu;
import com.eportal.domain.TLock;
import com.eportal.exception.NotUniqueException;
import com.eportal.hibernate.Finder;

import org.apache.log4j.Logger;
import org.hibernate.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.framework.jqgrid.JqGridPager;

import org.springframework.stereotype.Repository;

import javax.annotation.Resource;

/**
 * 统一数据访问接口实现
 *
 * @author dingyulei
 */
@Repository("dao")
public class BaseDAOImpl extends HibernateDaoSupport implements BaseDAO {

	@Autowired
	public JdbcTemplate yzJdbcTemplate;

	private Logger log = Logger.getLogger(BaseDAOImpl.class);

	@Resource(name = "sessionFactory")
	public void setSuperSessionFactory(SessionFactory sessionFactory) {
		super.setSessionFactory(sessionFactory);
	}

	public int countAll(String clazz) {
		final String hql = "select count(*) from " + clazz + " as a";
		Long count = (Long) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(hql);
				query.setMaxResults(1);
				return query.uniqueResult();
			}
		});
		return count.intValue();
	}

	public int countQuery(String hql) {
		final String counthql = hql;
		Long count = (Long) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(counthql);
				query.setMaxResults(1);
				return query.uniqueResult();
			}
		});
		return count.intValue();
	}

	/**
	 * 获得Finder的记录总数
	 * 
	 * @param finder
	 * @return
	 */
	public int countQueryResult(Finder finder) {
		Query query = getSession().createQuery(finder.getRowCountHql());
		finder.setParamsToQuery(query);
		if (finder.isCacheable()) {
			query.setCacheable(true);
		}
		return ((Number) query.iterate().next()).intValue();
	}

	public void delById(Class clazz, Serializable id) {
		Object object = getHibernateTemplate().load(clazz, id);
		getHibernateTemplate().delete(object);
	}

	public void delByConditions(String clazz, String conditions) {
		List list = getHibernateTemplate()
				.find("from " + clazz + (conditions == null || conditions.equals("") ? "" : " where " + conditions));
		getHibernateTemplate().deleteAll(list);
	}

	public List listAll(String clazz) {
		return getHibernateTemplate().find("from " + clazz + " as a order by a.id desc");
	}

	public void excute(String sql) {
		yzJdbcTemplate.execute(sql);
	}

	public int count(String sql) {
		return yzJdbcTemplate.queryForInt(sql);
	}

	public List listAll(String clazz, String parameter) {
		return getHibernateTemplate().find("from " + clazz + " as a "
				+ (parameter == null || parameter.equals("") ? "" : " where " + parameter) + " order by a.id asc");
	}

	public List listAll(String clazz, String parameter, String order) {
		return getHibernateTemplate().find(
				"from " + clazz + " as a " + (parameter == null || parameter.equals("") ? "" : " where " + parameter)
						+ " order by a." + (order == null || order.equals("") ? "id" : order) + " asc");
	}

	public List listAll(String clazz, String parameter, String order, String sort) {
		return getHibernateTemplate().find(
				"from " + clazz + " as a " + (parameter == null || parameter.equals("") ? "" : " where " + parameter)
						+ " order by a." + (order == null || order.equals("") ? "id" : order) + " "
						+ (sort == null || sort.equals("") ? "desc" : sort));
	}

	public List listAll(String clazz, int pageNo, int pageSize) {
		final int pNo = pageNo;
		final int pSize = pageSize;
		final String hql = "from " + clazz + " as a order by a.id desc";
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(hql);
				query.setMaxResults(pSize);
				query.setFirstResult((pNo - 1) * pSize);
				List result = query.list();
				if (!Hibernate.isInitialized(result))
					Hibernate.initialize(result);
				return result;
			}
		});
		return list;
	}
	
	public List list(final String hql, int pageNo, int pageSize) {
		final int pNo = pageNo;
		final int pSize = pageSize;
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(hql);
				query.setMaxResults(pSize);
				query.setFirstResult((pNo - 1) * pSize);
				List result = query.list();
				if (!Hibernate.isInitialized(result))
					Hibernate.initialize(result);
				return result;
			}
		});
		return list;
	}

	public List listAll(String clazz, JqGridPager jqGridPager) {
		final String hql = "from " + clazz + "  a  "
				+ (jqGridPager.getWhere() == null ? "" : " where " + jqGridPager.getWhere()) + "   order by a."
				+ jqGridPager.getSidx() + " " + jqGridPager.getSord();
		if (jqGridPager.getExporttype() == null || jqGridPager.getExporttype().equals("")
				|| (jqGridPager.getExporttype() != null && !jqGridPager.getExporttype().equals("all"))) {
			final int pNo = jqGridPager.getPager();
			final int pSize = jqGridPager.getRows();
			int count = this.countQuery("select count(*) " + hql);
			jqGridPager.setRecords(String.valueOf(count));
			List list = getHibernateTemplate().executeFind(new HibernateCallback() {
				public Object doInHibernate(Session session) throws HibernateException {
					Query query = session.createQuery(hql);
					query.setMaxResults(pSize);
					query.setFirstResult((pNo - 1) * pSize);
					List result = query.list();
					if (!Hibernate.isInitialized(result))
						Hibernate.initialize(result);
					return result;
				}
			});

			return list;
		} else {
			return getHibernateTemplate().find(hql);
		}
	}

	public Map<String, Object> list(Map<String, Object> params, JqGridPager jqGridPager) {
		StringBuffer sql = new StringBuffer();

		return null;
	}

	public Object loadById(Class clazz, Serializable id) {
		return getHibernateTemplate().load(clazz, id);
	}

	public Object loadObject(String hql) {
		final String hql1 = hql;
		Object obj = null;
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(hql1);
				return query.list();
			}
		});
		if (list.size() > 0)
			obj = list.get(0);
		return obj;
	}

	public List query(String hql, JqGridPager jqGridPager) {
		String hql1 = "select aa.* from (" + hql
				+ (jqGridPager.getWhere() == null ? "" : " where " + jqGridPager.getWhere()) 
				+ (jqGridPager.getSidx() == null ? "" : " order by " + jqGridPager.getSidx() + " " + jqGridPager.getSord()) 
				+ ") aa ";
		if (jqGridPager.getExporttype() == null || jqGridPager.getExporttype().equals("")
				|| (jqGridPager.getExporttype() != null && !jqGridPager.getExporttype().equals("all"))) {
			int pNo = jqGridPager.getPager();
			int pSize = jqGridPager.getRows();

			int count = yzJdbcTemplate.queryForInt("select count(*) from (" + hql1 + ") bb");
			jqGridPager.setRecords(String.valueOf(count));
			if (count == 0) {
				return null;
			}
			List list = yzJdbcTemplate.queryForList("select * from (select bb.*,rownum rownum_bb from (" + hql1
					+ ") bb where rownum<= " + (pNo * pSize) + ")" + " where rownum_bb>" + (pNo - 1) * pSize);
			if (list.size()<=0 && pNo>1) {
				pNo = pNo-1;
				list = yzJdbcTemplate.queryForList("select * from (select bb.*,rownum rownum_bb from (" + hql1
						+ ") bb where rownum<= " + (pNo * pSize) + ")" + " where rownum_bb>" + (pNo - 1) * pSize);
				jqGridPager.setPager(pNo);
			}
			return list;
		} else {
			return yzJdbcTemplate.queryForList(hql1);
		}
	}

	public List query(String hql, JqGridPager jqGridPager, String subquery) {
		String hql1 = "select aa.* from (" + hql
				+ (jqGridPager.getWhere() == null ? "" : " where " + jqGridPager.getWhere()) + "   order by "
				+ jqGridPager.getSidx() + " " + jqGridPager.getSord() + ") aa ";
		if (jqGridPager.getExporttype() == null || jqGridPager.getExporttype().equals("")
				|| (jqGridPager.getExporttype() != null && !jqGridPager.getExporttype().equals("all"))) {
			int pNo = jqGridPager.getPager();
			int pSize = jqGridPager.getRows();

			int count = yzJdbcTemplate.queryForInt("select count(*) from (" + hql1 + ") bb");
			jqGridPager.setRecords(String.valueOf(count));
			if (count == 0) {
				return null;
			}
			if (subquery != null && !subquery.equals("")) {
				subquery = "," + subquery;
			} else {
				subquery = "";
			}
			return yzJdbcTemplate.queryForList("select tt.* " + subquery
					+ " from (select * from (select bb.*,rownum rownum_bb from (" + hql1 + ") bb where rownum<= "
					+ (pNo * pSize) + ")" + " where rownum_bb>" + (pNo - 1) * pSize + ") tt");
		} else {
			return yzJdbcTemplate.queryForList(hql1);
		}

	}

	public List queryBySql(String sql) {
		return yzJdbcTemplate.queryForList(sql);
	}

	public List queryBySqlObjs(String sql) {
		final String sql_ = sql;
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createSQLQuery(sql_);
				return query.list();
			}
		});
	}

	public List query(String hql) {
		final String hql1 = hql;
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(hql1);
				return query.list();
			}
		});
	}

	public List query(String hql, int pageNo, int pageSize) {
		final int pNo = pageNo;
		final int pSize = pageSize;
		final String hql1 = hql;
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(hql1);
				query.setMaxResults(pSize);
				query.setFirstResult((pNo - 1) * pSize);
				List result = query.list();
				if (!Hibernate.isInitialized(result))
					Hibernate.initialize(result);
				return result;
			}
		});
	}

	public void save(Object obj) throws Exception{
		getHibernateTemplate().save(obj);
	}

	public void delete(Object obj) {
		getHibernateTemplate().delete(obj);
	}
	
	public int update(String hql) {
		final String hql1 = hql;
		return ((Integer) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createQuery(hql1);
				return query.executeUpdate();
			}
		})).intValue();
	}

	public void update(Object entity) {
		getHibernateTemplate().update(entity);
	}

	public int updateBySql(String hql) {
		final String hql1 = hql;
		return ((Integer) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				Query query = session.createSQLQuery(hql1);
				return query.executeUpdate();
			}
		})).intValue();
	}

	public Object insertOrUpdateBySql(final String sql, final Object[] objs, final int[] argTypes) {
		// int ret=yzJdbcTemplate.update(sql,objs, argTypes);
		KeyHolder keyHolder = new GeneratedKeyHolder();
		yzJdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql);
				for (int i = 0; i < objs.length; i++) {
					ps.setObject(i + 1, objs[i], argTypes[i]);
				}

				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey();
	}

	public Integer delBySql(final String sql, final Object[] obj) {
		return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
			public Integer doInHibernate(Session session) throws HibernateException, SQLException {
				SQLQuery q = session.createSQLQuery(sql);// 原文: SQLQuery q =
															// getSession().createSQLQuery(sql);
				if (obj != null && obj.length > 0) {
					for (int i = 0; i < obj.length; i++) {// 与JDBC不同的是，此处参数的索引是以0开始，而JDBC的PreparedStatement设置参数的索引是以1开始
						q.setParameter(i, obj[i]);
					}
				}
				return q.executeUpdate();
			}
		});

	}

	public Connection getConnection() {
		getHibernateTemplate().getSessionFactory().getCurrentSession().beginTransaction();
		return getHibernateTemplate().getSessionFactory().getCurrentSession().connection();
	}

	public Session getSessionMy() {
		getHibernateTemplate().getSessionFactory().getCurrentSession().beginTransaction();
		return getHibernateTemplate().getSessionFactory().getCurrentSession();
	}

	public List parameterLoad(String clazz, String where) {
		return getHibernateTemplate()
				.find("from " + clazz + " as a  " + (where == null ? "" : "where " + where) + "  order by a.id desc");

	}

	@Override
	public List find(Finder finder) {
		Query query = finder.createQuery(getSession());
		List list = query.list();
		return list;
	}

	public Object findFirst(Finder finder) {
		Query query = finder.createQuery(getSession());
		List list = query.list();
		if (list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	@Override
	public void rollback() {
		getSession().getTransaction().rollback();
		getSession().getTransaction().begin();
	}

	@Override
	public Object getById(Class clazz, Serializable id) {
		return this.getSession().get(clazz, id);
	}

	@Override
	public HibernateTemplate getTemplate() {
		return getHibernateTemplate();
	}

	@Override
	public List executeFind(final String sql, final String string, final List<Long> tmp) {
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
            @Override
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(sql)
                        .setParameterList(string, tmp);
                return query.list();

            }
        });
		return list;
	}

	@Override
	public List find(String sql, Object[] array) {
		return getHibernateTemplate().find(sql, array);
	}

	@Override
	public long findSequence(String sequen) {
		return yzJdbcTemplate.queryForLong("select "+sequen+".nextval from dual");
	}

	@Override
	public List executeFindS(final String sql, final String string, final List<String> ids) {
		List list = getHibernateTemplate().executeFind(new HibernateCallback() {
            @Override
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(sql)
                        .setParameterList(string, ids);
                return query.list();

            }
        });
		return list;
	}

	@Override
	public Object getFirst(String hql) {
		List list = query(hql, 1, 1);
		if(list.size()>0){
			return list.get(0);
		}
		return null;
	}

	@Override
	public Object getUniqueFirst(String hql) throws NotUniqueException {
		List list = query(hql, 1, 2);
		if(list.size()==1){
			return list.get(0);
		}else if(list.size()>1){
			throw new NotUniqueException();
		}
		return null;
	}

	//调用存储过程
	@Override
	public Map<String, Object> callProcedure(String callStr, String[] inList, String[] outList) throws Exception {
		// TODO Auto-generated method stub
		Map<String, Object> retMap  =  new HashMap();
		CallableStatement proc = null;
		proc = getConnection().prepareCall(callStr);
		for(int i=0;i<inList.length;i++){
			proc.setString(i+1, inList[i]);
		}
		int length = inList.length+1;
		for(int i=0;i<outList.length;i++){
			proc.registerOutParameter(i+length, Types.VARCHAR);
		}
		proc.execute();
		for(int i=0;i<outList.length;i++){
			retMap.put(outList[i], proc.getString(i+length));
		}
		return retMap;	
		}
	
	
}